calljdku912fandomcom-20200214-history
Importing and Exporting Data (R.Razavi)
Importing and Exporting Data Importing data is the process of retrieving data from sources external to Microsoft® SQL Server™ (for example, an ASCII text file) and inserting it into SQL Server tables. Exporting data is the process of extracting data from an instance of SQL Server into some user-specified format (for example, copying the contents of a SQL Server table to a Microsoft Access database).Importing data from an external data source into an instance of SQL Server is likely to be the first step you perform after setting up your database. After data has been imported into your SQL Server database, you can start to work with the database.Importing data into an instance of SQL Server can be a one-time occurrence (for example, migrating data from another database system to an instance of SQL Server). After the initial migration is complete, the SQL Server database is used directly for all data-related tasks, rather than the original system. No further data imports are required.Importing data can also be an ongoing task. For example, a new SQL Server database is created for executive reporting purposes, but the data resides in legacy systems updated from a large number of business applications. In this case, you can copy new or updated data from the legacy system to an instance of SQL Server on a daily or weekly basis.Usually, exporting data is a less frequent occurrence. SQL Server provides tools and features that allow applications, such as Access or Microsoft Excel, to connect and manipulate data directly, rather than having to copy all the data from an instance of SQL Server to the tool before manipulating it. However, data may need to be exported from an instance of SQL Server regularly. In this case, the data can be exported to a text file and then read by the application. Alternatively, you can copy data on an ad hoc basis. For example, you can extract data from an instance of SQL Server into an Excel spreadsheet running on a portable computer and take the computer on a business trip.SQL Server provides tools for importing and exporting data to and from data sources, including text files, ODBC data sources (such as Oracle databases), OLE DB data sources (such as other instances of SQL Server), ASCII text files, and Excel spreadsheets.Additionally, SQL Server replication allows data to be distributed across an enterprise, copying data between locations and synchronizing changes automatically between different copies of data. data from the database into other applications. Importing copies existing data from a text file to your database You can import data that you get from other applications into your web office database and export; exporting copies information from your database to a text file.Importing existing data saves you the trouble of typing it in by hand. You may need to import data only once, right before people start to use the database. For example, if you create a Customer Relationship Management (CRM) database, you could import existing customer information into it. You may also need to regularly import data, for example, from a weekly sales report. Exporting data allows you to use information from your database in another application outside your web office. For example, you may want to export data so that you can open it in Microsoft Excel® for charting or further analysis. Exporting is also an easy way to create a local backup of the data in your database. Importing: Before you begin Before you import information into your database, it's important to step back and examine both the text file you're importing and the database you're importing it into. You might be tempted to ignore the planning phase and start importing right away, but remember that taking a little time to plan your import now will probably save you a lot of time resolving import errors later on.Here are some things to think about before you import data from a text file: *The file must be a CSV or tab-delimited text file, and you must know which format it's in because you specify the format during the import process. *The database fields must already be defined in your web office database. Importing simply copies data from the text file into fields in your database; it does not create any database fields for you. *Importing is easier when the fields in the text file are in the same order as the fields in the database; that way, there are fewer fields to map. *If your database has required fields, the text file must contain values for each of these fields. If you are importing phone numbers that include a country code, you must insert a double pipe || between the country code and the rest of the number in your source text file. ' 'Importing text files To import a text file: #Open up the database into which you'd like to import data. #On any database View page, click the Import button in the Command Bar. If you're a database manager, you can also start from the Manage Database page. #Type the name of the text file to import or click the Browse... button to select the file. If the first row of the file contains field names instead of data, check the First row contains field names option. #Select the format of the file you are importing (CSV or tab delimited). #Click Next to go to the next page.